﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[spBkp_RemoveOldFiles]
#-- Purpose:		Deletes backup files that are not necessary to fulfill
#--					the recovery window defined at 
#--					[dbo].[tblConfiguration].[cfg_recover_window_days]
#--	Last Update:	05/04/2015
#--					For a complete history - please review comments in Version
#--					Control.
#------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[spBkp_RemoveOldFiles]
(
	@database_name		sysname
)
AS

SET NOCOUNT ON

--- Declare Local Variables
DECLARE @physical_device_name nvarchar(4000), @sError nvarchar(4000), @recovery_window int
DECLARE @tFiles TABLE (physical_device_name nvarchar(4000))
DECLARE		@progname sysname

--- Define the name of this program (for logs)
SELECT		@progname = 'spBkp_RemoveOldFiles'

--- Determine the configured Recovery Windows Days
SELECT		@recovery_window = ISNULL(cfg_recover_window_days, 8)
FROM		[dbo].[tblMaint_Config]

--- Default to 8 days if nothing is found in tblConfiguration
IF @recovery_window IS NULL
	SET @recovery_window = 8

--- Build the list of database files NOT needed to meet the Recovery Window Days
INSERT INTO @tFiles
	SELECT		bmf.physical_device_name
	FROM		[msdb].[dbo].[backupset] b
	JOIN		[msdb].[dbo].[backupmediafamily] bmf ON b.media_set_id = bmf.media_set_id
	LEFT JOIN	[msdb].[sys].[backup_devices] bd ON bmf.physical_device_name = bd.physical_name
				AND (
					bmf.device_type = bd.type
					OR (
						bmf.device_type IN (2, 102)
						AND bd.type IN (2, 102)
					)
				)
				AND bd.type IN (2, 102)
	WHERE		b.backup_set_id NOT IN (SELECT backup_set_id FROM [dbo].[fnBkp_GetBackupList_RecoveryWindow] (@database_name) t1)
				AND ISNULL(b.backup_start_date, '1/1/1900') < DATEADD(dd, (-1 * @recovery_window), GETDATE())
				AND b.database_name = @database_name
				AND bd.physical_name IS NULL

--- Delete the unnecessary files
DECLARE crs CURSOR FOR SELECT physical_device_name FROM @tFiles WHERE [dbo].[fnIO_FileExists] (physical_device_name) = 1
OPEN crs
FETCH NEXT FROM crs INTO @physical_device_name
WHILE @@FETCH_STATUS <> -1
  BEGIN
	--- Set the message
	SELECT	@sError = 'Deleting backup file: ' + @physical_device_name
	EXEC	[dbo].[spMaint_InsertLogEntry] @program_name = @progname, @message = @sError

	--- Delete the backup file
	BEGIN TRY
		EXEC	[dbo].[spIO_DeleteFile]  @filePath = @physical_device_name
	END TRY
	BEGIN CATCH
		SELECT	@sError = 'Failed to delete backup file: ' + @physical_device_name
		EXEC	[dbo].[spMaint_InsertLogEntry] @program_name = @progname, @message = @sError
	END CATCH
	
	--- Next
	FETCH NEXT FROM crs INTO @physical_device_name
  END
CLOSE crs
DEALLOCATE crs

SET NOCOUNT OFF